﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SolutionGenerator
{
    public partial class WizardGetTable : UserControlParent
    {
        public WizardGetTable(SolutionConfiguration configuration) : base(configuration)
        {
            InitializeComponent();
        }
        public override bool IsPageValid
        {
            get
            {
                return SolutionGeneratorListOfTables.CheckedItems.Count > 0;
            }
        }
        public override string ValidationMessage
        {
            get
            {
                return TextMessage.WizardValidation_GetTable;
            }
        }
        public override void Save()
        {
            List<SolutionConfigurationTableInfo> tables = new List<SolutionConfigurationTableInfo>();
            List<SolutionConfigurationTableInfo> existing = GetSolutionConfiguration.GetTableInfo;
            foreach (string tableName in SolutionGeneratorListOfTables.CheckedItems)
            {
                tables.Add(new SolutionConfigurationTableInfo()
                {
                    TableName = tableName,
                    IsProcess = true,
                    GetColumnInfo = CollectColumnTable(tableName)
                });
            }
            ProcessForeignKey(tables, GetSchemaForeignKey(GetSolutionConfiguration.ConnectionString));
            GetSolutionConfiguration.GetTableInfo = tables;
        }
        public override void LoadWizard()
        {
            CollectTableNameInformation();
            if (GetSolutionConfiguration.GetTableInfo != null)
            {
                foreach (SolutionConfigurationTableInfo table in GetSolutionConfiguration.GetTableInfo.Where(_ => _.IsProcess))
                {
                    for (int i = 0; i < SolutionGeneratorListOfTables.Items.Count; i++)
                    {
                        if ((string)SolutionGeneratorListOfTables.Items[i] == table.TableName)
                        {
                            SolutionGeneratorListOfTables.SetItemChecked(i, true);
                            break;
                        }
                    }
                }
            }
        }
        public override string WizardTitle
        {
            get
            {
                return TextMessage.WizardTitle_GetTableInfo;
            }
        }
        private void ProcessForeignKey(List<SolutionConfigurationTableInfo> tables, DataTable fkSchema)
        {
            foreach (DataRow row in fkSchema.Rows)
            {
                string tableName = (string)row[2];
                string columnName = (string)row[3];
                string fkTableName = (string)row[8];
                string fkColumnName = (string)row[9];
                string fkName=(string)row[16];
                SolutionConfigurationTableInfo baseTable = tables.SingleOrDefault(_ => _.TableName == tableName);
                if (baseTable != null)
                {
                    SolutionConfigurationColumnInfo baseColumn = baseTable.GetColumnInfo.SingleOrDefault(_ => _.ColumnName == columnName);
                    List<SolutionConfigurationForeignKeyInfo> fkInfo = baseColumn.FKList ?? new List<SolutionConfigurationForeignKeyInfo>();
                    fkInfo.Add(new SolutionConfigurationForeignKeyInfo()
                    {//this is child table
                        FKColumn = fkColumnName,
                        FKTable = fkTableName,
                        ForeignKeyName = fkName,
                        FKType = ForeignKeyType.Child
                    });
                    baseColumn.FKList = fkInfo;
                    if (tables.Count(_ => _.TableName == tableName) == 0)
                    {
                        tables.Add(new SolutionConfigurationTableInfo()
                        {
                            TableName = tableName,
                            IsFKChildProcess = false,
                            IsProcess = false,
                            GetColumnInfo = CollectColumnTable(tableName)
                        });
                    }
                }
                SolutionConfigurationTableInfo parentTable = tables.SingleOrDefault(_ => _.TableName == fkTableName);
                if (parentTable != null)
                {
                    SolutionConfigurationColumnInfo parentColumn = parentTable.GetColumnInfo.SingleOrDefault(_ => _.ColumnName == fkColumnName);
                    List<SolutionConfigurationForeignKeyInfo> fkInfo = parentColumn.FKList ?? new List<SolutionConfigurationForeignKeyInfo>();
                    fkInfo.Add(new SolutionConfigurationForeignKeyInfo()
                    {//this is parent table
                        FKColumn = columnName,
                        FKTable = tableName,
                        ForeignKeyName = fkName,
                        FKType = ForeignKeyType.Parent
                    });
                    parentColumn.FKList = fkInfo;
                    if (tables.Count(_ => _.TableName == tableName) == 0)
                    {
                        tables.Add(new SolutionConfigurationTableInfo()
                        {
                            TableName = tableName,
                            IsFKChildProcess = false,
                            IsProcess = false,
                            GetColumnInfo = CollectColumnTable(tableName)
                        });
                    }
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// <remarks>
        /// https://msdn.microsoft.com/en-us/library/cc668764.aspx
        /// oledbdatatype https://social.msdn.microsoft.com/Forums/en-US/81bef8f4-a648-48dd-8566-92a9fc3836c5/getschema-datatype?forum=adodotnetdataproviders
        /// more on filter http://www.databaseskill.com/2370945/
        /// </remarks>
        private List<SolutionConfigurationColumnInfo> CollectColumnTable(string tableName)
        {
            List<SolutionConfigurationColumnInfo> result = new List<SolutionConfigurationColumnInfo>();
            foreach (DataRow obj in GetSchemaColumn(GetSolutionConfiguration.ConnectionString, tableName).Rows)
            {
                SolutionConfigurationColumnInfo column = new SolutionConfigurationColumnInfo()
                {
                    ColumnName = (string)obj[3],
                    IsNullAble = (bool)obj[10],
                    ColumnDataType = (OleDbType)Enum.Parse(typeof(OleDbType), obj[11].ToString()),
                    DefaultValue = ((obj[8] as DBNull) != null) ? string.Empty : obj[8].ToString()
                };
                switch (column.ColumnDataType)
                {
                    case OleDbType.Char:
                    case OleDbType.VarChar:
                        column.ColumnLength = (Int64)obj[13];
                        break;
                }
                result.Add(column);
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        /// <remarks>
        /// 00 - TABLE_CATALOG
        /// 01 - TABLE_SCHEMA
        /// 02 - TABLE_NAME
        /// 03 - TABLE_TYPE
        /// 04 - TABLE_GUID
        /// 05 - DESCRIPTION
        /// 06 - TABLE_PROPID
        /// 07 - DATE_CREATED
        /// 08 - DATE_MODIFIED
        /// </remarks>
        private DataTable GetSchemaTable(string connectionString)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "TABLE" });
                connection.Close();
                return schemaTable;
            }
        }
        /// <summary>
        /// Get all relation info from DB
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// <remarks>
        /// 00 - PK_TABLE_CATALOG
        /// 01 - PK_TABLE_SCHEMA
        /// 02 - PK_TABLE_NAME
        /// 03 - PK_COLUMN_NAME
        /// 04 - PK_COLUMN_GUID
        /// 05 - PK_COLUMN_PROPID
        /// 06 - FK_TABLE_CATALOG
        /// 07 - FK_TABLE_SCHEMA
        /// 08 - FK_TABLE_NAME
        /// 09 - FK_COLUMN_NAME
        /// 10 - FK_COLUMN_GUID
        /// 11 - FK_COLUMN_PROPID
        /// 12 - ORDINAL
        /// 13 - UPDATE_RULE
        /// 14 - DELETE_RULE
        /// 15 - PK_NAME
        /// 16 - FK_NAME
        /// 17 - DEFERRABILITY
        /// /// </remarks>
        private DataTable GetSchemaForeignKey(string connectionString)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys,
                    new object[] { null, null, null, null });
                connection.Close();
                return schemaTable;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// <remarks>
        /// 00 - TABLE_CATALOG
        /// 01 - TABLE_SCHEMA
        /// 02 - TABLE_NAME
        /// 03 - COLUMN_NAME
        /// 04 - COLUMN_GUID
        /// 05 - COLUMN_PROPID
        /// 06 - ORDINAL_POSITION
        /// 07 - COLUMN_HASDEFAULT
        /// 08 - COLUMN_DEFAULT (return string)
        /// 09 - COLUMN_FLAGS
        /// 10 - IS_NULLABLE
        /// 11 - DATA_TYPE
        /// 12 - TYPE_GUID
        /// 13 - CHARACTER_MAXIMUM_LENGTH
        /// 14 - CHARACTER_OCTET_LENGTH
        /// 15 - NUMERIC_PRECISION
        /// 16 - NUMERIC_SCALE
        /// 17 - DATETIME_PRECISION
        /// 18 - CHARACTER_SET_CATALOG
        /// 19 - CHARACTER_SET_SCHEMA
        /// 20 - CHARACTER_SET_NAME
        /// 21 - COLLATION_CATALOG
        /// 22 - COLLATION_SCHEMA
        /// 23 - COLLATION_NAME
        /// 24 - DOMAIN_CATALOG
        /// 25 - DOMAIN_SCHEMA
        /// 26 - DOMAIN_NAME
        /// 27 - DESCRIPTION
        /// 28 - COLUMN_LCID
        /// 29 - COLUMN_COMPFLAGS
        /// 30 - COLUMN_SORTID
        /// 31 - COLUMN_TDSCOLLATION
        /// 32 - IS_COMPUTED
        /// 33 - SS_XML_SCHEMACOLLECTION_CATALOGNAME
        /// 34 - SS_XML_SCHEMACOLLECTION_SCHEMANAME
        /// 35 - SS_XML_SCHEMACOLLECTIONNAME
        /// 36 - SS_UDT_CATALOGNAME
        /// 37 - SS_UDT_SCHEMANAME
        /// 38 - SS_UDT_NAME
        /// 39 - SS_UDT_ASSEMBLY_TYPENAME
        /// 40 - SS_IS_SPARSE
        /// 41 - SS_IS_COLUMN_SET
        /// </remarks>
        private DataTable GetSchemaColumn(string connectionString, string tableName)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                    new object[] { null, null, tableName, null });
                connection.Close();
                return schemaTable;
            }
        }
        private void CollectTableNameInformation()
        {
            SolutionGeneratorListOfTables.Items.Clear();
            foreach (DataRow obj in GetSchemaTable(GetSolutionConfiguration.ConnectionString).Rows)
                SolutionGeneratorListOfTables.Items.Add((string)obj[2]);
        }
        private void SolutionGeneratorGetTableSelectAll_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < SolutionGeneratorListOfTables.Items.Count; i++)
                SolutionGeneratorListOfTables.SetItemChecked(i, SolutionGeneratorGetTableSelectAll.Checked);
        }
    }
}